package com.gusto.engine.colfil.matrix.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collection;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.gusto.engine.colfil.Prediction;
import com.gusto.engine.colfil.Rating;
import com.gusto.engine.colfil.matrix.MatrixDAO;

/**
 * <p>A <code>jdbc</code> implementation of the {@link MatrixDAO} interface.<br>
 * The implementation is based on Spring's jdbc template.<br>
 * The implementation has been tested with MySQL.</p>
 * 
 * @author amokrane.belloui@gmail.com
 * 
 */
public class MatrixDAOJdbcTemplateImpl implements MatrixDAO {
	
	private Logger log = Logger.getLogger(getClass());
	
	// FIXME Redesign Configurable values
	
	private String CONFIG_RATING;// = "matrix -> user_id, item_id, rating, timestamp";
	private String CONFIG_PREDICTION;// = "matrix_pred -> user_id, item_id, rating, timestamp";
	private String CONFIG_NORMALIZED;// = "matrix_norm -> user_id, item_id, rating, timestamp";
	
	public void setCONFIG_RATING(String config_rating) {
		CONFIG_RATING = config_rating;
	}
	public void setCONFIG_PREDICTION(String config_prediction) {
		CONFIG_PREDICTION = config_prediction;
	}
	public void setCONFIG_NORMALIZED(String config_normalized) {
		CONFIG_NORMALIZED = config_normalized;
	}
	
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	private ConfigItem getConfigItem(String tablecolumn) {
		try {
			ConfigItem configItem = new ConfigItem();
			configItem.table = tablecolumn.substring(0, tablecolumn.indexOf("->")).trim();
			String rest = tablecolumn.substring(tablecolumn.indexOf("->") + 2).trim();
			
			configItem.user = rest.substring(0, rest.indexOf(",")).trim();
			rest = rest.substring(rest.indexOf(",") + 1).trim();
			
			configItem.item = rest.substring(0, rest.indexOf(",")).trim();
			rest = rest.substring(rest.indexOf(",") + 1).trim();
			
			configItem.eval = rest.substring(0, rest.indexOf(",")).trim();
			rest = rest.substring(rest.indexOf(",") + 1).trim();
			
			configItem.timestamp = rest;
			return configItem;
		}
		catch (Exception ex) {
			return null;
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<Rating> getItemsRatedByUser(long userId, boolean normalized) {
		log.debug("Items rated by user " + userId);
		
		ConfigItem ci;
		if (normalized) {
			ci = getConfigItem(this.CONFIG_NORMALIZED);
		}
		else {
			ci = getConfigItem(this.CONFIG_RATING);
		}
		String sql = "select * from " + ci.table + " where " + ci.user + "=? ";
		sql += "and (" + ci.eval + " is not null) ";
		
		log.debug("SQL " + sql);
		Object[] params = new Object[] { userId };
		int[] types = new int[] { Types.INTEGER };
		List<Rating> result = jdbcTemplate.query(sql, params, types, new RatingRowMapper(normalized));
		return result;
	}
	
	@SuppressWarnings("unchecked")
	public List<Rating> getUsersHavingRatedItem(long itemId, boolean normalized) {
		log.debug("Users having rated item " + itemId);
		
		ConfigItem ci;
		if (normalized) {
			ci = getConfigItem(this.CONFIG_NORMALIZED);
		}
		else {
			ci = getConfigItem(this.CONFIG_RATING);
		}
		String sql = "select * from " + ci.table + " where " + ci.item + "=? ";
		sql += "and (" + ci.eval + " is not null) ";
		
		log.debug("SQL " + sql);
		
		Object[] params = new Object[] { itemId };
		int[] types = new int[] { Types.INTEGER };
		List<Rating> result = jdbcTemplate.query(sql, params, types, new RatingRowMapper(normalized));
		return result;
	}
	
	public double getUserMeanRating(long userId) {
		log.debug("User mean rating " + userId);
		
		ConfigItem ci = getConfigItem(this.CONFIG_RATING);
		String sql = "select avg(rating) * 1000 from " + ci.table + " group by " + ci.user + " having " + ci.user + "=? ";
		log.debug("SQL " + sql);
		
		Object[] params = new Object[] { userId };
		int[] types = new int[] { Types.NUMERIC };
		Double result = jdbcTemplate.queryForLong(sql, params, types) / 1000.0;
		return result;
	}
	
	public double getItemMeanRating(long itemId) {
		log.debug("Item mean rating " + itemId);
		
		ConfigItem ci = getConfigItem(this.CONFIG_RATING);
		String sql = "select avg(rating) * 1000 from " + ci.table + " group by " + ci.item + " having " + ci.item + "=? ";
		log.debug("SQL " + sql);
		
		Object[] params = new Object[] { itemId };
		int[] types = new int[] { Types.NUMERIC };
		try {
			Double result = jdbcTemplate.queryForLong(sql, params, types) / 1000.0;
			return result;
		}
		catch (Exception ex) {
			return 2.5;
		}
	}
	
	public void insertPrediction(long userId, long itemId, Prediction prediction) {
		log.debug("Insert prediction " + userId + ", " + itemId + " => " + prediction);
		
		ConfigItem ci = getConfigItem(this.CONFIG_PREDICTION);
		String sql_i = "insert into " + ci.table + " " + 
		"(" + ci.timestamp + ", " + ci.eval + ", " + ci.user + ", " + ci.item + ") values (?, ?, ?, ?)";
		
		String sql_u = "update " + ci.table + " " +
		"set " + ci.timestamp + " = ?, " + ci.eval + " = ? where " + ci.user + " = ? and " + ci.item + " = ? ";
		
		Object[] params = new Object[] { null, prediction.getValue(), userId, itemId };
		int[] types = new int[] { Types.TIMESTAMP, Types.DOUBLE, Types.INTEGER, Types.INTEGER };
		
		try {
			if (existsPrediction(userId, itemId)) {
				log.debug("Prediction exists");
				log.debug("SQL update " + sql_u);
				jdbcTemplate.update(sql_u, params, types);
			}
			else {
				log.debug("Prediction doesn't exist");
				log.debug("SQL insert " + sql_i);
				jdbcTemplate.update(sql_i, params, types);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void insertRating(long userId, long itemId, Rating rating, boolean checkExistence) {
		log.debug("Insert rating " + userId + ", " + itemId + " => " + rating);
		
		ConfigItem ci = getConfigItem(this.CONFIG_RATING);
		
		String sql_i = "insert into " + ci.table + " " +
		"(" + ci.timestamp + ", " + ci.eval + ", " + ci.user + ", " + ci.item + ") values (?, ?, ?, ?)";
		
		String sql_u = "update " + ci.table + " " +
		"set " + ci.timestamp + " = ?, " + ci.eval + " = ? where " + ci.user + " = ? and " + ci.item + " = ? ";
		
		Object[] params = new Object[] { rating.getTimeStamp(), rating.getValue(), userId, itemId };
		int[] types = new int[] { Types.TIMESTAMP, Types.DOUBLE, Types.INTEGER, Types.INTEGER };
		
		try {
			if (checkExistence) {
				if (existsRating(userId, itemId)) {
					log.debug("Rating exists");
					log.debug("SQL update " + sql_u);
					jdbcTemplate.update(sql_u, params, types);
				}
				else {
					log.debug("Rating doesn't exist");
					log.debug("SQL insert " + sql_i);
					jdbcTemplate.update(sql_i, params, types);
				}
			}
			else {
				jdbcTemplate.update(sql_i, params, types);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private boolean existsRating(long userId, long itemId) {
		ConfigItem ci = getConfigItem(this.CONFIG_RATING);
		String sql = "select count(*) from " + ci.table + " where " + ci.user + "=? and " + ci.item + "=? ";
		Object[] params = new Object[] { userId, itemId };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER };
		int count = jdbcTemplate.queryForInt(sql, params, types);
		log.debug("Exists rating " + userId + ", " + itemId + " => " + (count > 0));
		return (count > 0);
	}
	
	private boolean existsPrediction(long userId, long itemId) {
		ConfigItem ci = getConfigItem(this.CONFIG_PREDICTION);
		String sql = "select count(*) from " + ci.table + " where " + ci.user + "=? and " + ci.item + "=? ";
		Object[] params = new Object[] { userId, itemId };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER };
		int count = jdbcTemplate.queryForInt(sql, params, types);
		log.debug("Exists prediction " + userId + ", " + itemId + " => " + (count > 0));
		return (count > 0);
	}
	
	@SuppressWarnings("unchecked")
	public Prediction getPrediction(long userId, long itemId) {
		log.debug("Getting Prediction " + userId + ", " + itemId);
		
		ConfigItem ci = getConfigItem(this.CONFIG_PREDICTION);
		String sql = "select * from " + ci.table + " where " + ci.user + "=? and " + ci.item + "=? and " + ci.eval + " is not null ";
		Object[] params = new Object[] { userId, itemId };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER };
		
		List list = jdbcTemplate.query(sql, params, types, new PredictionRowMapper());
		if (list.size() > 0) {
			Prediction prediction = (Prediction)list.get(0); 
			log.debug("Prediction " + userId + ", " + itemId + " => " + prediction);
			return prediction;
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public Rating getRating(long userId, long itemId) {
		log.debug("Getting Rating " + userId + ", " + itemId);
		
		ConfigItem ci = getConfigItem(this.CONFIG_RATING);
		String sql = "select * from " + ci.table + " where " + ci.user + "=? and " + ci.item + "=? and " + ci.eval + " is not null ";
		Object[] params = new Object[] { userId, itemId };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER };
		
		List list = jdbcTemplate.query(sql, params, types, new RatingRowMapper(false));
		if (list.size() > 0) {
			Rating rating = (Rating)list.get(0);
			log.debug("Prediction " + userId + ", " + itemId + " => " + rating);
			return rating;
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<Rating> getSubMatrix(Collection<Long> usersIds, Collection<Long> itemsIds) {
		log.debug("Getting Sub Matrix " + usersIds + " - " + itemsIds);
		// FIXME Normalized or not
		//ConfigItem ci = getConfigItem(this.CONFIG_NORMALIZED);
		ConfigItem ci = getConfigItem(this.CONFIG_RATING);
		
		String sql_userCriteria = "(";
		for (Object userId : usersIds) {
			sql_userCriteria += ci.user + "=" + userId + " or ";
		}
		sql_userCriteria += " false)";
		
		String sql_itemCriteria = "(";
		for (Object itemId : itemsIds) {
			sql_itemCriteria += ci.item + "=" + itemId + " or ";
		}
		sql_itemCriteria += " false)";
		
		String sql = "select * from " + ci.table + " where " + sql_userCriteria + " and " + sql_itemCriteria + " ";
		sql += "and (" + ci.eval + " is not null) ";
		
		log.debug("SQL " + sql);
		
		Object[] params = new Object[] { };
		int[] types = new int[] { };
		// FIXME Normalized or not
		List<Rating> result = jdbcTemplate.query(sql, params, types, new RatingRowMapper(false));
		return result;
		
	}
	
	class RatingRowMapper implements RowMapper {
		boolean normalized;
		public RatingRowMapper(boolean normalized) {
			this.normalized = normalized;
		}
		
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			ConfigItem ci;
			if (normalized) {
				ci = getConfigItem(CONFIG_NORMALIZED);
			}
			else {
				ci = getConfigItem(CONFIG_RATING);
			}
			Rating eval = new Rating();
			eval.setUserId(rs.getInt(ci.user));
			eval.setItemId(rs.getInt(ci.item));
			try{
				eval.setTimeStamp(rs.getDate(ci.timestamp));
			}
			catch (Exception ex) {}
			eval.setValue(Double.parseDouble(rs.getObject(ci.eval).toString()));
			
		    return eval;
		}
	}

	class PredictionRowMapper implements RowMapper {
		public PredictionRowMapper() {
		}
		
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			ConfigItem ci = getConfigItem(CONFIG_PREDICTION);
			Prediction eval = new Prediction();
			eval.setUserId(rs.getInt(ci.user));
			eval.setItemId(rs.getInt(ci.item));
			eval.setValue( Double.parseDouble(rs.getObject(ci.eval).toString()));
			
			return eval;
		}
	}
	
	private class ConfigItem {
		public String table;
		public String user;
		public String item;
		public String eval;
		public String timestamp;
	}
	
}
